package com.bjtu.transactionclient.service.impl;

import com.bjtu.transactionclient.api.constant.Constans;
import com.bjtu.transactionclient.api.constant.IndexCodeToTable;
import com.bjtu.transactionclient.api.constant.SQLContant;
import com.bjtu.transactionclient.api.utils.StringSimilarityUtil;
import com.bjtu.transactionclient.base.result.Result;
import com.bjtu.transactionclient.base.result.SuccessEnum;
import com.bjtu.transactionclient.core.ConflictProcessor;
import com.bjtu.transactionclient.dao.SyncDao;
import com.bjtu.transactionclient.dao.UserDao;
import com.bjtu.transactionclient.dao.UserIndexDao;
import com.bjtu.transactionclient.dao.UserUpdateOfVersionAndTableDao;
import com.bjtu.transactionclient.domain.RecordInfo;
import com.bjtu.transactionclient.domain.SendSqlData;
import com.bjtu.transactionclient.domain.User;
import com.bjtu.transactionclient.domain.UserIndexNoTable;
import com.bjtu.transactionclient.service.SyncService;
import com.bjtu.transactionclient.utils.LocalDateTimeAndStringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
public class SyncServiceImpl implements SyncService {

    @Autowired
    private SyncDao syncDao;

    @Autowired
    private UserIndexDao userIndexDao;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    UserDao userDao;

    @Autowired
    ConflictProcessor conflictProcessor;


    @Override
    public synchronized List<Result> sendSqlMsg(List<SendSqlData> sendSqlDataList)
    {
        //取标志位
        String curUserNo=sendSqlDataList.get(0).getUserNo();
        //移除标志位
        sendSqlDataList.remove(0);
        List<Result> results = new ArrayList<>();
        if(sendSqlDataList.size() !=0) {
            Map<String, List<SendSqlData>> listGroupByIndexNo = groupListByIndexNo(sendSqlDataList);
            for (Map.Entry<String, List<SendSqlData>> entry : listGroupByIndexNo.entrySet()) {//根据indexNo分组:IndexNo->SendSqlData
                String indexNo = entry.getKey();
                List<SendSqlData> sqlDataByIndexNo = entry.getValue();
                System.out.println("indexNo:" + indexNo);
                if (indexNo == null || indexNo.length() == 0 || indexNo.equals("null")) {
                    //不涉及著录项的操作 约定情况N ********************************************************
                    //Has indexNo? No
                    //要操作的数据没有indexNo，即不涉及文献操作，应该先同步这部分数据
                    executeSql(sqlDataByIndexNo);
                } else {
                    //Has indexNo
                    //根据用户作用原理，涉及索引号的操作必存在userindextable中的sql语句,先定位到userindextable
                    UserIndexNoTable serverValue = syncDao.selectByIndexNo(indexNo);
                    User requestUser = userDao.queryUserNoByIndex(curUserNo);
//                    User requestUser=jdbcTemplate.queryForObject("select * from usertable where UserNo="+representer.getUserNo(),
//                            com.bjtu.transactionclient.domain.User.class);

                    if (serverValue == null) {
                        //服务端数据库中没有 该索引号
                        String content = sqlDataByIndexNo.get(0).getContent();
                        List<String> values = getValues(extraTableName(sqlDataByIndexNo));
//                        String[]values = jdbcTemplate.queryForObject("select * from "+representer.getTableName(),
//                                java.lang.String[].class);
                        boolean flag = false;
                        User serverUser = null;
                        for (String value : values) {
                            if (StringSimilarityUtil.cos(content, value) < Constans.SIMILARITY_THRESHOLD) {
                                //NY型
                                flag = true;
                                serverValue = syncDao.selectByIndexNo(value.split(" ")[0]);
                                serverUser = userDao.queryUserNoByIndex(serverValue.getUserNo());
//                                serverUser = jdbcTemplate.queryForObject("select * from usertable where UserNo="+serverValue.getUserNo(),
//                                        com.bjtu.transactionclient.domain.User.class);
                                break;
                            }
                        }
                        if (flag) {
                            //NY型 即中心数据库没有相同的索引号，但存在相似的文献 需要矛盾处理.
                            conflictProcessor.ProcessNY(requestUser.getUserRole(), serverUser.getUserRole(),
                                    serverValue.getStatus(), sqlDataByIndexNo, results);
                        } else {
                            executeSql(sqlDataByIndexNo);
                        }
                    } else {
                        //服务端数据库有 该索引号
                        String serverTable = IndexCodeToTable.getTableName(serverValue.getIndexCode()).getTableName();
                        String serverIndexCode = serverValue.getIndexCode();
                        String clientTable = "", clientContent = "";
                        User serverUser = userDao.queryUserNoByIndex(serverValue.getUserNo());
//                        User serverUser = jdbcTemplate.queryForObject("select * from usertable where UserNo="+serverValue.getUserNo(),
//                                com.bjtu.transactionclient.domain.User.class);
                        for (SendSqlData sqlData : sqlDataByIndexNo) {
                            if (!sqlData.getTableName().equals("userindexnotable")) {
                                //clientIndexCode = extratIndexCode(sqlData.getSql());
                                clientTable = sqlData.getTableName();
                            } else {
                                clientContent = sqlData.getContent();
                            }
                        }
                        //if(!clientIndexCode.equals(serverIndexCode))
                        if (!clientTable.equals(serverTable)) {
                            //矛盾判断 YN :即有相同的索引号 但不是同一文献(不是同一张表)
                            conflictProcessor.ProcessYN(requestUser.getUserRole(), serverUser.getUserRole(),
                                    serverValue.getStatus(), sqlDataByIndexNo, results);
                        } else {  //矛盾判断 YN :存在相同的索引号 属于同一张表 比较记录的相似度
                            IndexCodeToTable indexCodeToTable = IndexCodeToTable.getTableName(serverIndexCode);
                            String tableName = indexCodeToTable.getTableName();
                            String serverValues = SelectInfoFromTableOnServer(tableName, indexNo);
                            if (StringSimilarityUtil.cos(clientContent, serverValues) < Constans.SIMILARITY_THRESHOLD) {
                                continue;
                            } else {
                                //矛盾判断YN
                                conflictProcessor.ProcessYN(requestUser.getUserRole(), serverUser.getUserRole(),
                                        serverValue.getStatus(), sqlDataByIndexNo, results);
                            }
                        }
                    }
                }
            }
        }
        AppendChanges(curUserNo,results);
        return results;
    }

    @Autowired
    private UserUpdateOfVersionAndTableDao userUpdateOfVersionAndTableDao;

    private synchronized void AppendChanges(String curUser,List<Result> results) {
        //beginTime为上一次同步时间
        String beginTime = userUpdateOfVersionAndTableDao.queryByUserNo(curUser);
        BufferedReader bufferedReader = null;
        try {
            //1.读取txt
            File file = ResourceUtils.getFile("Status.txt");
            bufferedReader = new BufferedReader(new InputStreamReader(
                    new FileInputStream(file), "UTF-8"));
            String lineTxt = null;
            while ((lineTxt = bufferedReader.readLine()) != null) {
                //只读取上一次同步数据之后的
                if (lineTxt.equals("")||lineTxt.split(";")[1].compareTo(beginTime) <= 0)
                    continue;
                results.add(Result.success(lineTxt.split(";")[0], SuccessEnum.USERINDEXNO_NEW));
            }
            bufferedReader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        userUpdateOfVersionAndTableDao.updateDate(curUser,
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
    }

    //从一组涉及同一索引号著录文献中提取出文献所在表
    private String extraTableName(List<SendSqlData> sqls)
    {
        for(SendSqlData sql:sqls)
        {
            if(!sql.getTableName().equals("authorindexnotable")&&
            !sql.getTableName().equals("userindexnotable"))
            {
                return sql.getTableName();
            }
        }
        return null;
    }

    private List<String> getValues(String tablename)
    {
        String sql="select * from " + tablename + ";";
        try {
            List<Map<String, Object>> map = jdbcTemplate.queryForList(sql);

            List<String> buffer=new ArrayList<>();
            for(Map<String, Object> s:map)
                buffer.add( s.values().toString()
                        .replace("[", "")
                        .replace("]", "")
                        .replace(" ", ""));
            return  buffer;
        }
        catch (Exception e)
        {
            return null;
        }
    }
//    @Override
//    public synchronized List<Result> sendSqlMsg(List<SendSqlData> sendSqlDataList) {
//        List<Result> results =new ArrayList<>();
//        Map<String, List<SendSqlData>> listGroupByIndexNo =groupListByIndexNo(sendSqlDataList);
//        for (Map.Entry<String, List<SendSqlData>> entry : listGroupByIndexNo.entrySet()) {//根据indexNo分组:IndexNo->SendSqlData
//            String indexNo = entry.getKey();
//            List<SendSqlData> sqlDataByIndexNo = entry.getValue();
//            System.out.println("indexNo:"+indexNo);
//            if (indexNo==null||indexNo.length()==0||indexNo.equals("null")){
//                //要操作的数据没有indexNo，即不涉及文献操作，应该先同步这部分数据
//                executeSql(sqlDataByIndexNo);
//            }else{//涉及文献操作
//                List<SendSqlData> sendsqlList = entry.getValue();
//                Map<String, List<SendSqlData>> listGroupByIndexAndOperation = groupListByOperation(sendsqlList);//每一个分组再根据操作类型分组:Operation->SendSqlData
//                if(listGroupByIndexAndOperation.containsKey(SQLContant.INSERT)){//如果包含insert
//                    //1. 判断当前数据库有没有该索引号
//                    UserIndexNoTable userIndexNoTable = syncDao.selectByIndexNo(indexNo);
//                    if(userIndexNoTable==null){
//                        System.out.println("当前数据库中没有该索引号，不考虑用户级别时直接执行该索引号的所有操作");
//                        //当前数据库中没有该索引号，不考虑用户级别时直接执行该索引号的所有操作
//                        executeSql(sqlDataByIndexNo);
//                        continue;
//
//                    }
//                    // 2 当前数据库有该索引号,则判断需不需要更新索引号
//                    List<SendSqlData> sqlDatas = listGroupByIndexAndOperation.get(SQLContant.INSERT);//提取该索引号下的所有插入语句
//                    String newIndexNo = "";
//                    for (SendSqlData sqlData:sqlDatas) {
//                        if(sqlData.getTableName().equals("userindexnotable")){
//                            //2.1  首先提取插入userindexnotable的sql语句
//                            String sql = sqlData.getSql();//sql语句
//                            //2.2  获取IndexCode字段的值
//                            String indexCode = extratIndexCode(sql);
//                            //2.1.1 首先判断二者的IndexCode是不是一致
//                            //如果不是，说明一定不是同一文献，则重新生成索引号后执行
//                            if(!indexCode.equals(userIndexNoTable.getIndexNo())){
//                                System.out.println("不是同一种文献，需重新生成索引号后插入");
//                                newIndexNo = generateNewIndexNo(indexNo);
//                                break;
//                            }else { //如果是，根据IndexCode找到对应的表，之后进行相似性检测
//                                IndexCodeToTable indexCodeToTable = IndexCodeToTable.getTableName(indexCode);
//                                String tableName = indexCodeToTable.getTableName();
//                                //获得表中相应的数据
//                                //找到客户端发来的这条语句中的文献信息
//                                String sqlValues=sql.split("value")[1]
//                                        .replace("(","")
//                                        .replace(")","")
//                                        .replace(" ","");//原sql语句中的value,去掉括号和空格
//
//                                //服务器端该信息
//                                String serverValues = SelectInfoFromTableOnServer(tableName,indexNo);
//                                if (StringSimilarityUtil.cos(sqlValues, serverValues) < Constans.SIMILARITY_THRESHOLD){
//                                    //3.1 不相似，修改索引号后插入
//                                    newIndexNo = generateNewIndexNo(indexNo);
//                                    System.out.println("两文献不相似，需修改索引号后插入");
//                                    break;
//                                }else {//相似，暂时不做任何操作
//                                    System.out.println("两文献相似，暂时不做任何操作");
//                                    break;
//                                }
//                            }
//                        }
//                    }
//                    if(newIndexNo.length() > 0){//则重新生成了索引号
//                        //先将原先的索引号替换成新的
//                        for (SendSqlData sqlData:sqlDataByIndexNo) {
//                            sqlData.setSql(sqlData.getSql().replace(indexNo,newIndexNo));
//                            if(sqlData.getTableName().equals("userindexnotable")){
//                                //返回给客户端的信息，并让客户端执行修改，只需返回userindexnotable的sql语句
//                                results.add(Result.success(sqlData.getSql(), SuccessEnum.USERINDEXNO_NEW));
//                            }
//                        }
//                        executeSql(sqlDataByIndexNo);
//                    }
//
//                }else {//没有insert操作，直接执行sql
//                    System.out.println("没有insert操作，直接执行sql");
//                    executeSql(sqlDataByIndexNo);
//                    continue;
//                }
//            }
//        }
//        return results;
//    }

    @Override
    public synchronized List<RecordInfo> otherTeamUserInfo(User curUser, String date) {
        List<RecordInfo> recordInfoList = new ArrayList<>();
        //先获得该用户同团队的其他成员
        List<String> userNos = userDao.findAllMembers(curUser.getUserTeamNo(),curUser.getUserNo());
        //获得每个成员的indexNo且更新时间大于该用户上一次更新时间
        List<UserIndexNoTable> userIndexNoTableList = new ArrayList<>();
        for (String userNo:userNos){
            List<UserIndexNoTable> userIndexNoTables = userIndexDao.queryUserIndexNoByUserNo(userNo,date);
            userIndexNoTableList.addAll(userIndexNoTables);
        }
        for (UserIndexNoTable userIndexNoTable : userIndexNoTableList){
            //先把userindexnotable的信息放入
            RecordInfo recordInfo1 =new RecordInfo();
            recordInfo1.setTableName("userindexnotable");
            recordInfo1.setValues(SelectInfoFromTableOnServer("userindexnotable",userIndexNoTable.getIndexNo()));
            recordInfo1.setIndexNo(userIndexNoTable.getIndexNo());
            recordInfoList.add(recordInfo1);
            //再把其他的表的信息放入
            String  indexCode= userIndexNoTable.getIndexCode();
            RecordInfo recordInfo =new RecordInfo();
            String tableName = IndexCodeToTable.getTableName(indexCode).getTableName();
            recordInfo.setTableName(tableName);
            recordInfo.setValues(SelectInfoFromTableOnServer(tableName,userIndexNoTable.getIndexNo()));
            recordInfo.setIndexNo(userIndexNoTable.getIndexNo());
            recordInfoList.add(recordInfo);
        }
        return recordInfoList;
    }

    private synchronized String SelectInfoFromTableOnServer(String tableName, String indexNo) {
        String selectSql = "select * from "+tableName+" where IndexNo=\"" +indexNo+"\"";
        System.out.println("selectSql:"+selectSql);
        Map<String, Object> map = jdbcTemplate.queryForMap(selectSql);
        String mapvlues=map.values().toString()
                .replace("[","")
                .replace("]","")
                .replace(" ","");//查询出来的信息，去掉中括号和空格
        return mapvlues;
    }

    private synchronized String extratIndexCode(String sql) {
        String sqlValues=sql.split("value")[1]
                .replace("(","")
                .replace(")","")
                .replace(" ","");

        String []values =sqlValues.split(",");
        String indexCode = values[3];
        return indexCode.replace("\"","");
    }

    private synchronized void executeSql(List<SendSqlData> sql) {
        for (SendSqlData sendData:sql) {
            System.out.println("sql:"+sendData.getSql());
            try {
                jdbcTemplate.update(sendData.getSql());
                writeToSql(sendData.getSql());
            }catch (Exception e) {
                if (e instanceof DuplicateKeyException) {//捕获主键重复异常，主要用于没有indexNo的表中的插入
                    String tableName = sendData.getSql().split(" ")[2];
                    //String id = sendData.getKeyInfo().split("=")[0];
                    String selectsql = " select count(*) from " + tableName;
                    String size = jdbcTemplate.queryForObject(selectsql, java.lang.String.class);
                    int keyValue = Integer.valueOf(size) + 1;
                    String keyorigin = sendData.getKeyInfo().split("\"")[1];
                    String s = sendData.getSql().replace(keyorigin, String.valueOf(keyValue));
                    jdbcTemplate.update(s);
                    writeToSql(s);
                }
            }
        }
    }

    /*
     * 保存到txt的格式：sql语句;主键
     * */
    private void writeToSql(String sendSqlData) {
        //我们不记录userupdateofverisonandtables表
        if(sendSqlData.contains("userupdateofverisonandtables"))
            return;
        BufferedWriter writer = null;
        try {
            File file = ResourceUtils.getFile("Status.txt");
            //File file = new File(ClassLoader.getSystemResource("canalsql.txt").toURI());
            writer  = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file,true), "UTF-8"));
            writer.append(sendSqlData+";"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
            writer.append("\r\n");
            writer.flush();
            writer.close();
        } catch(FileNotFoundException e) {
            System.out.println("File Not Found");
        }catch (IOException e) {
            e.printStackTrace();
        }
    }

    /*
    将list按照IndexNo分组
     */
    public synchronized Map<String, List<SendSqlData>> groupListByIndexNo(List<SendSqlData> sendSqlDataList) {
        Map<String, List<SendSqlData>> map = sendSqlDataList.stream().collect(Collectors.groupingBy(SendSqlData::getIndexNo));
        return map;
    }
    /*
    将list按照Operation分组
     */
    public synchronized Map<String, List<SendSqlData>> groupListByOperation(List<SendSqlData> sendSqlDataList) {
        Map<String, List<SendSqlData>> map = sendSqlDataList.stream().collect(Collectors.groupingBy(SendSqlData::getOperation));
        return map;
    }

    private synchronized String generateNewIndexNo(String indexNo) {
        String prefix = indexNo.substring(0,indexNo.length()-1) + '%';
        char code = indexNo.charAt(indexNo.length()-1);
        List<String> indexNoList =  userIndexDao.queryUserIndexNoLike(prefix);
        for (String index : indexNoList) {
            char lastChar = index.charAt(index.length()-1);//取最后一个字符
            if (lastChar<code) continue;
            else if (lastChar==code) code++;
            else if (lastChar>code) break;
        }

        return prefix.substring(0,prefix.length()-1)+code;
    }
    /**
     * 返回索引号下一个字母
     * @param indexNoList
     * @return
     */
//    private Character LastIndexCodeLetter(List<String> indexNoList){
//        char result = 'A';
//        for (int i = 0; i < indexNoList.size(); i++){
//            String str = indexNoList.get(i);
//            char t = str.charAt(str.length()-1);
//            if (t > result){
//                result = t;
//            }
//        }
//        if (result < 'Z'){
//            return (char)(result+1);
//        }
//        return result;
//    }



}